import mysql.connector
import pandas as pd
import dash
import dash_core_components as dcc
import dash_html_components as html
import plotly.express as px

# 连接到SAKILA数据库
cnx = mysql.connector.connect(
    host='localhost',
    user='root',
    password='123456',
    database='sakila'
)

# 查询数据
query = """
    SELECT c.name AS category, COUNT(*) AS count
    FROM film f
    JOIN film_category fc ON f.film_id = fc.film_id
    JOIN category c ON fc.category_id = c.category_id
    GROUP BY c.name
    ORDER BY count DESC
    LIMIT 5
"""
df_category = pd.read_sql_query(query, cnx)

query = """
    SELECT a.actor_id, a.first_name, a.last_name, COUNT(*) AS count
    FROM actor a
    JOIN film_actor fa ON a.actor_id = fa.actor_id
    GROUP BY a.actor_id, a.first_name, a.last_name
    ORDER BY count DESC
    LIMIT 5
"""
df_actor = pd.read_sql_query(query, cnx)

# 关闭数据库连接
cnx.close()

# 创建Dash应用
app = dash.Dash(__name__)

# 创建布局
app.layout = html.Div(
    children=[
        html.H1("SAKILA数据库仪表板"),
        html.Div(
            children=[
                html.Div(
                    children=[
                        dcc.Graph(
                            figure=px.bar(
                                df_category,
                                x='category',
                                y='count',
                                labels={'category': '电影分类', 'count': '电影数量'},
                                title='热门电影分类'
                            )
                        )
                    ],
                    className='six columns'
                ),
                html.Div(
                    children=[
                        dcc.Graph(
                            figure=px.bar(
                                df_actor,
                                x='last_name',
                                y='count',
                                labels={'last_name': '演员', 'count': '电影数量'},
                                title='热门演员排名'
                            )
                        )
                    ],
                    className='six columns'
                )
            ],
            className='row'
        ),
        html.Div(
            children=[
                html.Div(
                    children=[
                        # 添加更多的KPI图表
                        # ...
                    ],
                    className='six columns'
                ),
                html.Div(
                    children=[
                        # 添加更多的KPI图表
                        # ...
                    ],
                    className='six columns'
                )
            ],
            className='row'
        )
    ]
)

# 运行应用
if __name__ == '__main__':
    app.run_server(debug=True)